package com.whnk.job.task;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.whnk.job.util.CommonUtils;
import com.whnk.job.util.ConnectionUtil;

/**
 * 定时任务基础服务---执行存储过程，类中方法，一般情况下，不用修改
 *
 * @author whnk
 */
@Service
public  class LoadSrcDataService {
    private final Logger logger = LoggerFactory.getLogger(LoadSrcDataService.class);

    @Autowired
    ConnectionUtil connectionUtil;

    private Connection connectionInAndOut;

    private String querySql  = "select tx_date,amt,sum1,loan_amt, " +
    							" br_code,br_name,br_level,up_br_code,up_br_name," +
    							" CUSTOMER_CD,CUSTOMER_NM ,TTSHAST_TPLV_CGYCD,TTSHAST_TPLV_CGYNM " +
    							"from src_t_index_info  t where t.tx_date = ?";
    private String insertSql = "INSERT INTO src_t_index_info(tx_date,amt,sum1,loan_amt, " +
    							" br_code,br_name,br_level,up_br_code,up_br_name, " +
    							" CUSTOMER_CD,CUSTOMER_NM ,TTSHAST_TPLV_CGYCD,TTSHAST_TPLV_CGYNM) " +
    							"VALUES ( ?, ?, ?, ?, " +
    							" ?, ?, ?, ?, ?, " +
    							" ?, ?, ?, ?) " ;

    private String deleteSql = "DELETE from  src_t_index_info s ";

    public PreparedStatement queryPreparedStatement;
    public PreparedStatement insertPreparedStatement;
    public PreparedStatement deletePreparedStatement;

//    public static void main(String[] args) {
//    	String fileName="d:\\src_t_index_info.txt";
//    	new LoadSrcDataService().doLoad(fileName);
//    }

    /**
     *
     * @Title: doLoad
     * @Description: TODO(描述这个方法的作用)
     * @param: @param modelId
     * @return: void
     * @throws
     */
    public  void doLoad(String fileName) {
    	//1:读取文件
        logger.info("文件名："+fileName);
        File file = new File(fileName);
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new FileReader(file));
            String currLineString ;
            int i = 0;
            logger.info("开始读取文件");
            while ((currLineString = reader.readLine()) != null) {
                //2:一行行导入数据表
            	loadOneLine2Database(currLineString);
            	i++;
                logger.info("第【"+i+"】行：【"+currLineString+"】");
            }
            reader.close();
            logger.info("共有:"+i+"条记录");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

    public  void init() {
    	try {
    		openConnection();
            prepareSqlStatement();
        } catch (Exception e) {
            logger.error("数据连接失败");
        }
    }



    /**
     *
     * @Title: loadDatabase
     * @Description: 导入一行数据
     * @param: @param oneLineString
     * @return: void
     * @throws
     */
    private  void loadOneLine2Database(String oneLineString) {
        try {
            String[] lines= oneLineString.split(",");
        	loadOneData2SrcIndexTable(insertPreparedStatement,lines);
        } catch (Exception e) {
        	 logger.error("数据导入失败");
        	 e.printStackTrace();

        }
    }


    /**
     * 打开数据库连接
     * @param
     */
    private void openConnection() {
        connectionInAndOut = connectionUtil.getConnection();
        if (connectionInAndOut == null) {
            logger.error("获取数据库连接失败");
            throw new RuntimeException("获取数据库连接失败");
        }
    }


    /**
     * 预编译sql，提高效率
     *
     */
    private void prepareSqlStatement() {
        try {
        	queryPreparedStatement = connectionInAndOut.prepareStatement(querySql);
        	insertPreparedStatement = connectionInAndOut.prepareStatement(insertSql);
        	deletePreparedStatement = connectionInAndOut.prepareStatement(deleteSql);
        } catch (SQLException e) {
            logger.error("预编译语句失败", e);
            throw new RuntimeException("prepare statement failed");
        }
    }




    /**
     * 日志数据库句柄
     * @param
     */
    public void releaseConnection() {
        CommonUtils.CloseHandle(queryPreparedStatement);
        CommonUtils.CloseHandle(insertPreparedStatement);
        CommonUtils.CloseHandle(deletePreparedStatement);
        CommonUtils.CloseHandle(connectionInAndOut);
    }

    /**
     *
     * @Title: logBatchRunInfo
     * @Description: 写入一行数据
     * @param: @param preparedStatement
     * @param: @param lines
     * @param: @throws SQLException
     * @return: void
     * @throws
     */
    private void loadOneData2SrcIndexTable(PreparedStatement preparedStatement,String[]lines) throws SQLException {
    	/**
    	 * INSERT INTO `src_t_index_info(	tx_date,amt,sum1,loan_amt,
											br_code,br_name,br_level,up_br_code,up_br_name,
											CUSTOMER_CD,CUSTOMER_NM ,TTSHAST_TPLV_CGYCD,TTSHAST_TPLV_CGYNM)
				VALUES ( 					'20210901', 10.0000, 1, 10000.0000,
											'420000000', '湖北建行', '2', '999999999', '中国建设银行',
											'001', '京北方', 'C001', '贷款');
    	 */
    	for(int i =0 ;i < 13 ;i ++) {
    		preparedStatement.setString(i+1, lines[i]);
    	}
    	logger.info("赋值成功");
        preparedStatement.setDouble(2,  Double.parseDouble(lines[1]));
        preparedStatement.setLong(3, Long.parseLong(lines[2]));
        preparedStatement.setDouble(4, Double.parseDouble(lines[3]));

        preparedStatement.execute();
    }

}
